package crud.util;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.List;
import java.util.Objects;

@Slf4j
public class SqlUtil {
    static Connection dbCon;

    private static JSONArray fetchAllDict(ResultSet rs) throws Exception {
        ResultSetMetaData md = rs.getMetaData();
        int num = md.getColumnCount();
        JSONArray rows = new JSONArray();
        while (rs.next()) {
            JSONObject row = new JSONObject(true);
            for (int i = 1; i <= num; i++) {
                row.put(md.getColumnLabel(i).toLowerCase(), rs.getObject(i));
            }
            rows.add(row);
            if (rows.size() >= 10000) {
                break;
            }
        }
        return rows;
    }

    public synchronized static Connection getCrudDbCon() throws Exception {
        if (dbCon == null) {
            Class.forName("org.sqlite.JDBC");
            dbCon = DriverManager.getConnection("jdbc:sqlite:crud.db");
        }
        return dbCon;
    }

    public static String getLimitString(int page, int perPage, String sqlMode) {
        if (page <= 1) {
            page = 1;
        }

        int offset = (page - 1) * perPage;

        if (Objects.equals(sqlMode, "ORACLE")) {
            return " WHERE ROWNUM BETWEEN " + offset + " AND " + (offset + perPage);
        } else if (Objects.equals(sqlMode, "POSTGRESQL")) {
            return (offset <= 0 ? "" : " OFFSET " + offset) + " LIMIT " + perPage;
        } else if (Objects.equals(sqlMode, "SQLSERVER")) {
            return " OFFSET " + offset + " ROWS FETCH FIRST " + perPage + " ROWS ONLY";
        } else {
            return " LIMIT " + perPage + (offset <= 0 ? "" : " OFFSET " + offset);  // DELETE, UPDATE 不支持 OFFSET
        }

    }

    static public JSONArray executeQuery(Connection dbCon, String sql) throws Exception {
        JSONArray rows;
        Statement dbCur = null;
        try {
            dbCur = dbCon.createStatement();
            ResultSet rs = dbCur.executeQuery(sql);
            rows = fetchAllDict(rs);
        } catch (Exception ex) {
            log.error(ex.getMessage());
            throw new Exception(ex.getMessage());
        } finally {
            try {
                if (dbCur != null) {
                    dbCur.close();
                }
            } catch (Exception ex) {
                log.error(ex.getMessage());
            }
        }

        return rows;
    }

    static public JSONArray executeQuery(Connection dbCon, String sql, List<Object> paramList) throws Exception {
        JSONArray rows;
        PreparedStatement dbCur = null;
        try {
            dbCur = dbCon.prepareStatement(sql);
            if (paramList != null && !paramList.isEmpty()) {
                int parameterIndex = 1;
                for (Object param : paramList) {
                    dbCur.setObject(parameterIndex, param);
                    parameterIndex += 1;
                }
            }
            ResultSet rs = dbCur.executeQuery();
            rows = fetchAllDict(rs);

        } catch (Exception ex) {
            log.error(ex.getMessage());
            throw new Exception(ex.getMessage());
        } finally {
            try {
                if (dbCur != null) {
                    dbCur.close();
                }
            } catch (Exception ex) {
                log.error(ex.getMessage());
            }
        }
        return rows;
    }

    static public int executeUpdate(Connection dbCon, String sql) throws Exception {
        Statement dbCur = null;
        int count = -1;
        try {
            dbCur = dbCon.createStatement();
            count = dbCur.executeUpdate(sql);
        } catch (Exception ex) {
            log.error(ex.getMessage());
            throw new Exception(ex.getMessage());
        } finally {
            try {
                if (dbCur != null) {
                    dbCur.close();
                }
            } catch (Exception ex) {
                log.error(ex.getMessage());
            }
        }
        return count;
    }

    static public int executeUpdate(Connection dbCon, String sql, List<Object> paramList) throws Exception {
        PreparedStatement dbCur = null;
        int count = -1;
        try {
            dbCur = dbCon.prepareStatement(sql);
            if (paramList != null && !paramList.isEmpty()) {
                int parameterIndex = 1;
                for (Object param : paramList) {
                    dbCur.setObject(parameterIndex, param);
                    parameterIndex += 1;
                }
            }
            count = dbCur.executeUpdate();

        } catch (Exception ex) {
            log.error(ex.getMessage());
            throw new Exception(ex.getMessage());
        } finally {
            try {
                if (dbCur != null) {
                    dbCur.close();
                }
            } catch (Exception ex) {
                log.error(ex.getMessage());
            }
        }
        return count;
    }

    static public String executeAdd(Connection dbCon, String sql, List<Object> paramList) throws Exception {
        PreparedStatement dbCur = null;
        String id = null;
        try {
            dbCur = dbCon.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            if (paramList != null && !paramList.isEmpty()) {
                int parameterIndex = 1;
                for (Object param : paramList) {
                    dbCur.setObject(parameterIndex, param);
                    parameterIndex += 1;
                }
            }
            dbCur.executeUpdate();
            ResultSet rs = dbCur.getGeneratedKeys();
            if (rs != null && rs.next()) {
                id = rs.getString(1);
            }
        } catch (Exception ex) {
            log.error(ex.getMessage());
            throw new Exception(ex.getMessage());
        } finally {
            try {
                if (dbCur != null) {
                    dbCur.close();
                }
            } catch (Exception ex) {
                log.error(ex.getMessage());
            }
        }
        return id;
    }

}
